



import java.beans.Statement;
import java.io.IOException;
import java.sql.*;
import java.util.List;
import java.util.Vector;
import net.java.dev.jaxb.array.StringArray;
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Stefano
 */
public class GDB{
         private static String DB_DRIVER="org.postgresql.Driver" ;
	private static String DB_CONNECTION="jdbc:postgresql://localhost:5432/gruppo12" ;
	private static String DB_USER="postgres" ;
	private static String DB_PASSWORD="gruppo12" ;
    
        public static void connetti() {
      
        
        
        System.out.println("-------- PostgreSQL "
				+ "JDBC Connection Testing ------------");
   try {
 
			Class.forName(DB_DRIVER);
 
		} catch (ClassNotFoundException e) {
 
			System.out.println("Where is your PostgreSQL JDBC Driver? "
					+ "Include in your library path!");
			e.printStackTrace();
			return;
 
		}
  System.out.println("PostgreSQL JDBC Driver Registered!");
 
		Connection connection = null;
 try {
 
			connection = DriverManager.getConnection(
					DB_CONNECTION,DB_USER,DB_PASSWORD);
 
		} catch (SQLException e) {
 
			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
			return;
 
		}
 
		if (connection != null) {
			System.out.println("You made it, take control your database now!");
		} else {
			System.out.println("Failed to make connection!");
		}// TODO code application logic here
    }//check della connessione al db
 //check delle primary key ritorna true se riuscito
	public boolean controllo(String value,String key,String Tabella) throws SQLException{
            Connection dbConnection = null;
		
             
 
		String selectTableSQL = "SELECT "+key
				+ " FROM "
				+ Tabella;
                
           try{ dbConnection = getDBConnection();
                 dbConnection.setAutoCommit(false);
                 java.sql.Statement statement = dbConnection.createStatement();
                java.sql.ResultSet rs = statement.executeQuery(selectTableSQL);
                while(rs.next()){if (rs.getString(1).toUpperCase().equals(value.toUpperCase())) return false;}
                return true;
              }
           catch (SQLException e) {
 
			System.out.println(e.getMessage());
			dbConnection.rollback();
                        return false;
 
		} finally {
                           if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
            
        }
 
	public  Boolean InserimentoAnagrafica(String Nome,String Indirizzo,String Piva,String Tel1,String Tel2,String Tel3,String Email1,String Email2,int tipo) throws SQLException {
 
		Connection dbConnection = null;
		PreparedStatement preparedStatementInsert = null;
             
 
		String insertTableSQL = "INSERT INTO test.elencoanagrafica"
				+ "(Nome,Indirizzo,Piva,Tel1,Tel2,Tel3,Email1,Email2,tipo) VALUES"
				+ "(?,?,?,?,?,?,?,?,?)";
 
		
 
 
		try {
			dbConnection = getDBConnection();
 
			dbConnection.setAutoCommit(false);
 
			preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
			preparedStatementInsert.setString(1, Nome.toUpperCase());
			preparedStatementInsert.setString(2, Indirizzo.toUpperCase());
			preparedStatementInsert.setString(3, Piva.toUpperCase());
                        preparedStatementInsert.setString(4, Tel1.toUpperCase());
                        preparedStatementInsert.setString(5, Tel2.toUpperCase());
                        preparedStatementInsert.setString(6, Tel3.toUpperCase());
                        preparedStatementInsert.setString(7, Email1.toUpperCase());
                        preparedStatementInsert.setString(8, Email2.toUpperCase());
                        preparedStatementInsert.setInt(9, tipo);
                        
                       if(controllo (Piva,"Piva","test.elencoanagrafica"));
		
                  
                        int rs = preparedStatementInsert.executeUpdate();
			
 
			
 
			dbConnection.commit();
 
			if (rs == 1 ){
                            System.out.println("Done!");
                            return true;
                        }
                         
                        return false;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
			dbConnection.rollback();
                        return false;
 
		} finally {
 
			if (preparedStatementInsert != null) {
				preparedStatementInsert.close();
			}
 
			
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
 //rilascia i contenuti della tabella del tipo di anagrafica voluto
	public  String [][] VisualizzaAnagrafica(int tipo) throws SQLException {
 
		Connection dbConnection = null;
		java.sql.Statement statement = null;
               String insertTableSQL = "SELECT nome,indirizzo,piva,tel1,tel2,tel3,email1,email2 "
				+ "FROM test.elencoanagrafica WHERE tipo LIKE "+ tipo;
                                  
			
 
		
 
 
		try {
			dbConnection = getDBConnection();
 
			dbConnection.setAutoCommit(false);
 
			statement = dbConnection.createStatement(1004, 1008);
		
                        
                   
		 
                    
                       java.sql.ResultSet rs = statement.executeQuery(insertTableSQL);
		      
                       String[][] results=null;
                       
                       int rows=0;
                       while(rs.next())//scorre all'ultimo record del select
                       {rows++;}
                       results= new String[rows][];    
                       rs.beforeFirst();                      //ritorna il cursore all'inizio
                       int i=0;
                       while (rs.next()){
                           String[] record = new String[8];
                           record[0]=rs.getString(1);
                           record[1]=rs.getString(2);
                           record[2]=rs.getString(3);
                           record[3]=rs.getString(4);
                           record[4]=rs.getString(5);
                           record[5]=rs.getString(6);
                           record[6]=rs.getString(7);
                           record[7]=rs.getString(8);
                           results[i]=record ;
                           i++;
                       }
                
 
			
 
			dbConnection.commit();
 
			return results;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
			dbConnection.rollback();
                        return null;
 
		} finally {
 
			
 
			
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
 
        public Boolean CancellaAnagrafica(String key) throws SQLException{
                Connection dbConnection = null;
		PreparedStatement preparedStatementInsert = null;
             
 
		String query = "DELETE "
				+ "FROM test.elencoanagrafica where piva = (?)";
     
     
     try {
			dbConnection = getDBConnection();
 
			dbConnection.setAutoCommit(false);
                        preparedStatementInsert = dbConnection.prepareStatement(query);
			preparedStatementInsert.setString(1, key);
                        
                     
		
                  
                        int rs = preparedStatementInsert.executeUpdate();
			
 
			
 
			dbConnection.commit();
 
			if (rs == 1 ){
                            System.out.println("Done!");
                            return true;
                        }
                         
                        return false;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
			dbConnection.rollback();
                        return false;
 
		} finally {
 
			if (preparedStatementInsert != null) {
				preparedStatementInsert.close();
			}
 
			
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 }
  
        public  String [][] CercaAnagrafica(String filter) throws SQLException {
 
		Connection dbConnection = null;
		java.sql.Statement statement = null;
               String insertTableSQL = "SELECT * "
				+ "FROM test.elencoanagrafica WHERE nome LIKE '"+ filter.toUpperCase()+"%'";
                                  
			
 
		
 
 
		try {
			dbConnection = getDBConnection();
 
			dbConnection.setAutoCommit(false);
 
			statement = dbConnection.createStatement(1004, 1008);
		
                        
                   
		 
                    
                       java.sql.ResultSet rs = statement.executeQuery(insertTableSQL);
		      
                       String[][] results=null;
                       
                       int rows=0;
                       while(rs.next())//scorre all'ultimo record del select
                       {rows++;}
                       results= new String[rows][];    
                       rs.beforeFirst();                      //ritorna il cursore all'inizio
                       int i=0;
                       while (rs.next()){
                           String[] record = new String[8];
                           record[0]=rs.getString(1);
                           record[1]=rs.getString(2);
                           record[2]=rs.getString(3);
                           record[3]=rs.getString(4);
                           record[4]=rs.getString(5);
                           record[5]=rs.getString(6);
                           record[6]=rs.getString(7);
                           record[7]=rs.getString(8);
                           results[i]=record ;
                           i++;
                       }
                
 
			
 
			dbConnection.commit();
 
			return results;
		        
                
                } catch (SQLException e) {
 
			System.out.println(e.getMessage());
			dbConnection.rollback();
                        return null;
 
		} finally {
 
			
 
			
 
			if (dbConnection != null) {
				dbConnection.close();
			}
 
		}
 
	}
   
  
        
 
  
  
  public Connection getDBConnection() {
 
		Connection dbConnection = null;
 
		try {
 
			Class.forName(DB_DRIVER);
 
		} catch (ClassNotFoundException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		try {
 
			dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
					DB_PASSWORD);
			return dbConnection;
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
		return dbConnection;
 
	}
 
  private static java.sql.Timestamp getCurrentTimeStamp() {
 
		java.util.Date today = new java.util.Date();
		return new java.sql.Timestamp(today.getTime());
 
	}
 



}

